ajReadFile function

Available since AlchemyJ v4.0

Description

The ajReadFile function imports the content of a file into a defined area in Excel worksheet. It is mainly used for reading data from a text file (All files without formatting and that can be edited directly by a text editor can be considered "text file", such as .txt, .csv, .json etc.) to Excel for processing.

Syntax

ajReadFile(file_location, [column_separator], [left_enclosure_character], [right_enclosure_character], [begin_at_line], [number_of_lines], [character_set], [skip_empty_row], [run_condition], [run_by_function_point_only])

Argument Name	Argument Type	Description
file_location (required)	String	The path of the file to be read.
column_separator (optional)	String	The character that is used to split input record into fields. In a CSV file, a comma is usually used.
left_enclosure_character (optional)	String	The left enclosure character. When a Column_separator is used, a field value itself can contain a column separator character. For example, when using a comma-delimited format and with a field containing "Cathy, Chow" as its actual value, the field would be separated into two fields as there is a comma in it. Using an enclosure character in writing and reading operations can solve this problem. The default value is an empty string (“”) which means no enclosure character. The character length cannot exceed 1 and must be used with parameter right_enclosure_character
right_enclosure_character (optional)	String	The right enclosure character. The default value is an empty string (“”) which means no enclosure character. The character length cannot exceed 1 and must be used with parameter left_enclosure_character
begin_at_line (optional)	Double	The starting line to begin the extraction of the content of the file. It will start from the beginning by default.
number_of_lines (optional)	Double	The number of lines to be extracted from the file. It will read all the lines starting from Begin_at_line and till the end of the file by default.
character_set (optional)	String	The character set used when writing the file. Some common character set codes are UTF-8 (ISO 10646 Unicode), GB2312 (Chinese Simplified), Big5 (Chinese Traditional) and us-ascii. The default value is UTF-8.
skip_empty_row (optional)	Boolean	If it equals FALSE, the empty rows will be read from the file. If it equals TRUE, the empty rows will not be read from the file. The default value is FALSE.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Contents of the selected file

2) Return Type: Multiple values (array formula)

Example

Here are some examples of using the ajReadFile function.

Example 1 - Read all record from file

The file content as below

The value of parameters and formula as below, the file is read line by line and each cell contains the whole line.

Because the file content is separated by commas , we fill 'column_separator' with comma-separated value, and the fields are separated by commas.

Example 2 - Dynamic the file path

In this example, it reads the data from an external file, the file path can be dynamic, specify comma as column_separator, " as the left_enclosure_character , " as the right_enclosure_character, extract the record start the second line and extract lines number is 4, when it has empty line skip it and set character to UTF-8.

First, you need to config the file path parameter in the %%AppConfig and set a name to this parameter

Then base on the scenario required to set the parameter value as below, then run this function, will read the read from the file.

Example 3 - Character_set setting

In this function the character_set is default to UTF-8, when the character set is not UTF-8 for the file, need to set correct character set. Otherwise, garbled characters may appear.

For above file the character set is not UTF-8, when don't set the special character_set, garbled characters may appear.

After set the character_set, the value display normally.

Click here to download the use case workbooks for further reference.

## Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Invalid file location.
The length of parameter left_enclosure_character and right_enclosure_character exceed 1.
Fill in the value of one of parameter left_enclosure_character and right_enclosure_character.
Character set is not supported.